package uy.edu.um.ltic.server.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import uy.edu.um.ltic.exception.DataBaseException;
import uy.edu.um.ltic.server.pool.DataBaseConnectionMgr;

public class PendingDAOMgr implements PendingDAOMgt {

	private static PendingDAOMgr instance = null;

	private PendingDAOMgr() {
	}

	public static PendingDAOMgr getInstance() {
		if (instance == null) {
			instance = new PendingDAOMgr();
		}
		return instance;
	}

	@Override
	public Connection openConnection() throws DataBaseException {
		Connection oConnection = null;
		try {
			oConnection = DataBaseConnectionMgr.getInstance().getConnection();
		} catch (SQLException e2) {
			 throw new DataBaseException();
		}
		return oConnection;
	}

	@Override
	public void createAll() throws DataBaseException {
		Connection oConnection = this.openConnection();
		createPendingTextMessages(oConnection);
		createPendingConferenceMessages(oConnection);
		try {
			oConnection.close();
		} catch (SQLException e) {
			 throw new DataBaseException();
		}
	}

	private void createPendingTextMessages(Connection oConnection) throws DataBaseException {
		String createTableT = "CREATE TABLE PendingTextMessages (receiver INT, "
				+ "sender INT, "
				+ "PRIMARY KEY (receiver, sender), "
				+ "FOREIGN KEY (receiver) REFERENCES Employees(nEmployee) ON DELETE CASCADE ON UPDATE CASCADE, "
				+ "FOREIGN KEY (sender) REFERENCES Employees(nEmployee) ON DELETE CASCADE ON UPDATE CASCADE);";
		Statement oStatementT;
		try {
			oStatementT = oConnection.createStatement();
			oStatementT.execute(createTableT);
		} catch (SQLException e) {
			 throw new DataBaseException();
		}
	}

	private void createPendingConferenceMessages(Connection oConnection) throws DataBaseException {
		String createTableT = "CREATE TABLE PendingConferenceMessages (receiver INT, "
				+ "idConference INT, "
				+ "PRIMARY KEY (receiver, idConference), "
				+ "FOREIGN KEY (receiver) REFERENCES Employees(nEmployee) ON DELETE CASCADE ON UPDATE CASCADE, "
				+ "FOREIGN KEY (idConference) REFERENCES Conference(idConference) ON DELETE CASCADE ON UPDATE CASCADE);";
		Statement oStatementT;
		try {
			oStatementT = oConnection.createStatement();
			oStatementT.execute(createTableT);
		} catch (SQLException e) {
			 throw new DataBaseException();
		}
	}

	@Override
	public void addPendingTextMessages(int receiverDisconnected, int sender) throws DataBaseException {
		Connection oConnection = this.openConnection();
		String insert = "INSERT INTO PendingTextMessages (receiver, sender) VALUES (?, ?)";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(insert);

			oPreparedStatement.setInt(1, receiverDisconnected);
			oPreparedStatement.setInt(2, sender);

			oPreparedStatement.execute();
			oPreparedStatement.close();
		} catch (SQLException e) {
			 throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				 throw new DataBaseException();
			}
		}
	}

	@Override
	public ArrayList<Integer> getPendingTextMessages(int receiverDisconnected) throws DataBaseException {
		Connection oConnection = this.openConnection();
		ArrayList<Integer> result = new ArrayList<Integer>();
		String get = "SELECT sender FROM PendingTextMessages WHERE receiver = ?";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(get);

			oPreparedStatement.setInt(1, receiverDisconnected);
			
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()){
				result.add(oResult.getInt(1));
			}
			oResult.close();
			oPreparedStatement.close();
		} catch (SQLException e) {
			 throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				 throw new DataBaseException();
			}
		}
		return result;
	}

	@Override
	public void deletePendingTextMessages(int receiverDisconnected) throws DataBaseException {
		Connection oConnection = this.openConnection();
		String delete = "DELETE FROM PendingTextMessages WHERE receiver = ?";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(delete);

			oPreparedStatement.setInt(1, receiverDisconnected);

			oPreparedStatement.execute();
			oPreparedStatement.close();
		} catch (SQLException e) {
			 throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				 throw new DataBaseException();
			}
		}
	}

	@Override
	public void addPendingConferenceMessages(int receiverDisconnected,
			int idConference) throws DataBaseException {
		Connection oConnection = this.openConnection();
		String insert = "INSERT INTO PendingConferenceMessages (receiver, idConference) VALUES (?, ?)";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(insert);

			oPreparedStatement.setInt(1, receiverDisconnected);
			oPreparedStatement.setInt(2, idConference);

			oPreparedStatement.execute();
			oPreparedStatement.close();
		} catch (SQLException e) {
			 throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				 throw new DataBaseException();
			}
		}
	}

	@Override
	public ArrayList<Integer> getPendingConferenceMessages(
			int receiverDisconnected) throws DataBaseException {
		Connection oConnection = this.openConnection();
		ArrayList<Integer> result = new ArrayList<Integer>();
		String get = "SELECT idConference FROM PendingConferenceMessages WHERE receiver = ?";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(get);

			oPreparedStatement.setInt(1, receiverDisconnected);
			
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()){
				result.add(oResult.getInt(1));
			}
			oResult.close();
			oPreparedStatement.close();
		} catch (SQLException e) {
			 throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				 throw new DataBaseException();
			}
		}
		return result;
	}

	@Override
	public void deletePendingConferenceMessages(int receiverDisconnected) throws DataBaseException {
		Connection oConnection = this.openConnection();
		String delete = "DELETE FROM PendingConferenceMessages WHERE receiver = ?";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(delete);

			oPreparedStatement.setInt(1, receiverDisconnected);

			oPreparedStatement.execute();
			oPreparedStatement.close();
		} catch (SQLException e) {
			 throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				 throw new DataBaseException();
			}
		}
	}

}
